﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;
using System.IO;
public partial class BCPhaiThuTheoPC : System.Web.UI.Page
{
    double tongtien = 0;
    int pc;
    double t;
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    string frmName = "frmPhaiThu";
    static int f_SuDung = 0, f_Them = 0, f_Sua = 0, f_Xoa = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static DataTable dt;
    static int PageNumber = 6;
    static int CurrentPage = 1;
    static DataTable dtPrint;
    NpoiExport cl = new NpoiExport();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])))
            gstGetMess("Hết phiên làm việc, vui lòng đăng nhập để tiếp tục", "Default.aspx");
        else
        {
            if (!IsPostBack)
            {


                string idnguoidung = Session["IDUser"].ToString();
                string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

                DataTable dt2 = ex.GetData_Text(sql);
                if (dt2.Rows.Count > 0)
                {
                    f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                    f_Them = int.Parse(dt2.Rows[0]["Them"].ToString());
                    f_Sua = int.Parse(dt2.Rows[0]["Sua"].ToString());
                    f_Xoa = int.Parse(dt2.Rows[0]["Xoa"].ToString());
                    f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                    f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                }
                if (f_SuDung.Equals(1))
                {
                    CurrentPage = 1;
                    btnCancel_Click(sender, e);
                    Title = "Báo cáo phải thu theo phiếu chi | " + Header;



                    for (int i = 1; i <= 12; i++)
                    {
                        dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));

                    }
                    dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
                    dlThang.SelectedIndex = 0;

                    int year1 = DateTime.Now.Year - 3;
                    int year2 = DateTime.Now.Year;
                    for (int i = year2; i >= year1; i--)
                    {
                        dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));

                    }
                    dlNam.SelectedValue = year2.ToString();

                    txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
                    txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
                    dlNam.Visible = false;
                    LoadNhanVien_TimKiem();


                }
                else
                    gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");

            }
        }
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {

    }

    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }




    void LoadNhanVien_TimKiem()
    {
        getDate();
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        dropNhanVien.Items.Clear();
        dropNhanVien.DataSource = ex.GetData("sp_BaoCaoPhaiThuTheoPC_ListNhanVien", p);
        dropNhanVien.DataBind();
        dropNhanVien.Items.Insert(0, new ListItem("--Chọn nhân viên--", ""));
        dropNhanVien.SelectedIndex = 0;
    }





    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0 && x > 999) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0 && s > 999) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }



    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BaoCaoPhieuChiTH_List", p);
        GV.DataBind();

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GV.DataSource = ex.GetData("sp_BaoCaoPhieuChiTH_List", p);
            GV.DataBind();

        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BaoCaoPhieuChiTH_List", p);
        GV.DataBind();

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
    public static void MergeRows(GridView gridView)
    {
        for (int rowIndex = gridView.Rows.Count - 2; rowIndex >= 0; rowIndex--)
        {
            GridViewRow row = gridView.Rows[rowIndex];
            GridViewRow previousRow = gridView.Rows[rowIndex + 1];

            for (int i = 0; i < row.Cells.Count; i++)
            {
                if (row.Cells[i].Text.Equals(previousRow.Cells[i].Text))
                {
                    row.Cells[i].RowSpan = previousRow.Cells[i].RowSpan < 2 ? 2 : previousRow.Cells[i].RowSpan + 1;
                    previousRow.Cells[i].Visible = false;
                }
            }
        }
    }
    protected void GV_PreRender(object sender, EventArgs e)
    {
        MergeRows(GV);
    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadNhanVien_TimKiem();
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }

    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        btnPre.Visible = false;
        btnNext.Visible = false;
        btnFirst.Visible = false;
        getDate();
        if (dropNhanVien.SelectedIndex == 0)
        {

            p = new SqlParameter[4];
            p[0] = new SqlParameter("@PageNumber", 1);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[2].SqlDbType = SqlDbType.SmallDateTime;
            p[3] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[3].SqlDbType = SqlDbType.SmallDateTime;
            dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian", p);
            GV.DataSource = dt;
            GV.DataBind();
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian_Footer", p);
            LoadFooter();
            Button1.Visible = true;
            Button2.Visible = true;
            Button3.Visible = true;

        }
        else
        {
            //p = new SqlParameter[3];

            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@NhanVien", ex.GetGuid(dropNhanVien.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            GV.DataSource = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian_NhanVien", p);
            GV.DataBind();
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@NhanVien", ex.GetGuid(dropNhanVien.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian_NhanVien", p);
            LoadFooter();
            Button1.Visible = false;
            Button2.Visible = false;
            Button3.Visible = false;
        }
        btnFirst.Visible = false;
        btnPre.Visible = false; btnNext.Visible = false;

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }

    private void LoadFooter()
    {
        string[] field = { "sotien","sotienthanhtoan", "tonphaithu",  "sotienhoanthu", "tonconlaiphaithu",
                             "chithucte","tientheophieu"};
        if (dt.Rows.Count > 0)
        {
            GV.FooterRow.Cells[2].Text = "Tổng cộng";
            GV.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
            for (int k = 9; k <=14; k++)
            {
                GV.FooterRow.Cells[k].Text = ConvertDecimal(double.Parse(dt.Compute("sum(" + field[k - 8] + ")", "").ToString()));
                GV.FooterRow.Cells[k].HorizontalAlign = HorizontalAlign.Right;
            }

            GV.FooterRow.Cells[6].Text = ConvertDecimal(double.Parse(dt.Compute("sum(" + field[0] + ")", "").ToString()));
            GV.FooterRow.Cells[6].HorizontalAlign = HorizontalAlign.Right;
        }

    }



    protected void Button1_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        p = new SqlParameter[4];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[2].SqlDbType = SqlDbType.SmallDateTime;
        p[3] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[3].SqlDbType = SqlDbType.SmallDateTime;
        dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian", p);
        GV.DataSource = dt;
        GV.DataBind();
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian_Footer", p);
        LoadFooter();

        Button2.Enabled = false;
        Button3.Enabled = true;
        Button1.Enabled = false;
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            Button2.Enabled = true;
            Button3.Enabled = true;
            Button1.Enabled = true;
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[2].SqlDbType = SqlDbType.SmallDateTime;
            p[3] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[3].SqlDbType = SqlDbType.SmallDateTime;
            dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian", p);
            GV.DataSource = dt;
            GV.DataBind();
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian_Footer", p);
            LoadFooter();

        }
        else
        {
            Button1.Enabled = false;
            Button2.Enabled = false;
            Button3.Enabled = true;
        }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        p = new SqlParameter[4];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[2].SqlDbType = SqlDbType.SmallDateTime;
        p[3] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[3].SqlDbType = SqlDbType.SmallDateTime;
        dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian", p);
        GV.DataSource = dt;
        GV.DataBind();
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        dt = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian_Footer", p);
        LoadFooter();

        if (GV.Rows.Count.Equals(0))
        {
            Button2.Enabled = true;
            Button3.Enabled = false;
        }
        else
        {
            Button2.Enabled = true;
            Button3.Enabled = true;
        }
        Button1.Enabled = true;
    }
    protected void btnIn_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            getDate();
            if (dropNhanVien.SelectedIndex == 0)
            {
                p = new SqlParameter[2];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;

                dtPrint = ex.GetData("sp_BaoCaoPhaiThuTheoPC_List_TheoThoiGian_Print", p);
            }
            PrintNPOI(dtPrint);
        }
        else gstGetMess("Bạn không có quyền in trang này", "");
    }

    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng tổng hợp";


            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text + " " + dlNam.SelectedItem.Text : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            //  string congtrinh = dlCongTrinh.SelectedIndex > 0 ? " - " + dlCongTrinh.SelectedItem.Text : "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Báo Cáo Phải Thu Theo Phiếu Chi " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //khachhang
            //rowIndex++;
            //row = sheet.CreateRow(rowIndex);
            //r1c1 = row.CreateCell(0);
            //r1c1.SetCellValue(dlKhachHang.SelectedIndex > 0 ? "Khách hàng: " + dlKhachHang.SelectedItem.Text + congtrinh : dlLoaiKH.SelectedIndex > 0 ? "Loại khách hàng: " + dlLoaiKH.SelectedItem.Text : "");
            //r1c1.CellStyle = hs3;
            //r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes

            #endregion
            //End header-----------------------------------------------------------------------

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày tháng", "Họ tên", "Bộ phận", "Chức vụ", "Nội dung chi", "Số PC", "Số tiền","Số PT hoàn",
                             "Số TT","Số tiền TT","Tồn phải thu","Số tiền hoàn thu","Tồn còn lại phải thu",
                             "Chi thực tế","Tiền Theo phiếu chi"
                              };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;

                sheet.AutoSizeColumn(j);

            }

            #endregion

            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j <= dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 11 || j == 12 || j == 10 || j == 13 || j == 14 || j == 15 || j == 7)
                    {
                        if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                        if (j <= 1)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }


            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "K", "L", "M", "N", "O", "P" };
            string[] footer1 = { "H" };
            //for (int k = 10; k <= 18; k++)
            //{

            //    cell = row.CreateCell(k + 1);
            //    cell.SetCellValue(GV.FooterRow.Cells[k].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[k].Text);
            //    cell.Row.Height = 500;
            //    cell.CellStyle = hs4;

            //}



            cell = row.CreateCell(7);
            cell.SetCellValue(GV.FooterRow.Cells[6].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[6].Text);
            cell.Row.Height = 500;
            cell.CellStyle = hs4;
            cell = row.CreateCell(10);
            cell.SetCellValue(GV.FooterRow.Cells[9].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[9].Text);
            cell.Row.Height = 500;
            cell.CellStyle = hs4;
            cell = row.CreateCell(11);
            cell.SetCellValue(GV.FooterRow.Cells[10].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[10].Text);
            cell.Row.Height = 500;
            cell.CellStyle = hs4;
            cell = row.CreateCell(12);
            cell.SetCellValue(GV.FooterRow.Cells[11].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[11].Text);
            cell.Row.Height = 500;
            cell.CellStyle = hs4;
            cell = row.CreateCell(13);
            cell.SetCellValue(GV.FooterRow.Cells[12].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[12].Text);
            cell.Row.Height = 500;
            cell.CellStyle = hs4;
            cell = row.CreateCell(14);
            cell.SetCellValue(GV.FooterRow.Cells[13].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[13].Text);
            cell.Row.Height = 500;
            cell.CellStyle = hs4;
            cell = row.CreateCell(15);
            cell.SetCellValue(GV.FooterRow.Cells[14].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[14].Text);
            cell.Row.Height = 500;
            cell.CellStyle = hs4;
            ////   fix lai border
            r1c1 = row.CreateCell(0);
            r1c1.CellStyle = hs4;
            r1c1.Row.Height = 400;

            r1c1 = row.CreateCell(1);
            r1c1.CellStyle = hs4;
            r1c1.Row.Height = 400;
            for (int i = 3; i <= 6; i++)
            {
                r1c1 = row.CreateCell(i);
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 400;
            }
            for (int i = 8; i <= 9; i++)
            {
                r1c1 = row.CreateCell(i);
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 400;
            }


            for (int ik = 0; ik < 20; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }


            //end sheet tong hop--------------------------

            //in tung tai khoan ngan hang
            #region tennhanvien
            string hoten = "";
            if (dropNhanVien.SelectedIndex.Equals(0))
            {

                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        hoten = dt.Rows[i]["hoten"].ToString();

                        if (!hoten.Equals(dt.Rows[i - 1]["hoten"].ToString()))
                        {
                            STT = 0;
                            //-----------Footer
                            #region Footer

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(2);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 10; k < 16; k++)
                            {

                                cell = row.CreateCell(k);
                                cell.CellFormula = "Sum(" + footer[k - 10] + "6:" + footer[k - 10] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;

                            }
                            cell = row.CreateCell(7);
                            cell.CellFormula = "Sum(" + footer1[0] + "6:" + footer1[0] + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            r1c1 = row.CreateCell(0);
                            r1c1.CellStyle = hs4;
                            r1c1.Row.Height = 400;

                            r1c1 = row.CreateCell(1);
                            r1c1.CellStyle = hs4;
                            r1c1.Row.Height = 400;
                            for (int ih = 3; ih <= 6; ih++)
                            {
                                r1c1 = row.CreateCell(ih);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                            for (int h = 8; h <= 9; h++)
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }


                            for (int ik = 0; ik < 20; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }

                            #endregion

                            //---End Footer

                            sheet = workbook.CreateSheet(cl.EscapeSheetName(hoten));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------

                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Báo Cáo Phải Thu Theo Phiếu Chi Nhân Viên " + hoten + " " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);



                            //freeze panes

                            #endregion
                            #region Header1-2
                            rowIndex++;
                            //header1


                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 1; j <= header.Length; j++)
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                            STT++;

                        }
                    }
                    else
                    {
                        hoten = "";
                        if (!hoten.Equals(dt.Rows[i]["hoten"].ToString()))
                        {
                            hoten = dt.Rows[i]["hoten"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(hoten));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Báo Cáo Phải Trả Thu Phiếu Chi Theo Nhân Viên " + hoten + " " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //nha cung cap



                            //freeze panes

                            #endregion

                            #region Header1-2
                            rowIndex++;
                            //header1

                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 1; j <= header.Length; j++)
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= dt.Columns.Count - 1; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                    #region ghidulieu
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j <= dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        if (j == 11 || j == 12 || j == 10 || j == 13 || j == 14 || j == 15 || j == 7)
                        {
                            if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                            }
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                            if (j == 6)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }
                    for (int ik = 0; ik < 20; ik++)
                    {
                        sheet.AutoSizeColumn(ik);
                    }
                    #endregion
                    if (i == dt.Rows.Count - 1)
                    {
                        #region Footer

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(2);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int k = 10; k < 16; k++)
                        {

                            cell = row.CreateCell(k);
                            cell.CellFormula = "Sum(" + footer[k - 10] + "6:" + footer[k - 10] + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                        }
                        cell = row.CreateCell(7);
                        cell.CellFormula = "Sum(" + footer1[0] + "6:" + footer1[0] + rowIndex.ToString() + ")";
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;
                        r1c1 = row.CreateCell(0);
                        r1c1.CellStyle = hs4;
                        r1c1.Row.Height = 400;

                        r1c1 = row.CreateCell(1);
                        r1c1.CellStyle = hs4;
                        r1c1.Row.Height = 400;
                        for (int im = 3; im <= 6; im++)
                        {
                            r1c1 = row.CreateCell(im);
                            r1c1.CellStyle = hs4;
                            r1c1.Row.Height = 400;
                        }
                        for (int ii = 8; ii <= 9; ii++)
                        {
                            r1c1 = row.CreateCell(ii);
                            r1c1.CellStyle = hs4;
                            r1c1.Row.Height = 400;
                        }


                        for (int ik = 0; ik < 20; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }


                        #endregion}
                    }
                }
            #endregion

                string ngaythang1 = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
                using (var exportData = new MemoryStream())
                {
                    workbook.Write(exportData);
                    string filename = dropNhanVien.SelectedIndex > 0 ? dropNhanVien.SelectedItem.Text : "";
                    string saveAsFileName = "BCPhaiThuTheoPC-" + filename + " " + ngaythang1 + ".xls";

                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                    Response.Clear();
                    Response.BinaryWrite(exportData.GetBuffer());
                    Response.End();

                }
            }
        }
    }
}